package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


@Mapper
public interface AdminDashboardMapper {
	
	
	
	//最近一次的渠道结算金额
	@Select(value=("SELECT a.chnl_name,(b.fee) AS fee FROM sys_chnl a  LEFT JOIN ( SELECT "
			+ "chnl_id,chnl_name,fee,balance FROM sys_chnl_bill WHERE FIND_IN_SET(cycle_id,f_get_MaxCycleid()))"
			+ " b ON a.chnl_id=b.chnl_id WHERE a.chnl_level = 1"))
	public List<Map<Object,Object>> getChnlBill();
	
	//战区销售数据排名top5
	@Select(value = ("SELECT f_get_chnlName(chnl_id) AS chnl_name,SUM(balance) AS sum FROM (SELECT chnl_id,user_id"
			+ " FROM sys_sales) a ,(select discnt_id,discnt_inst_id,create_user,balance,create_time from "
			+ "bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,c.balance,c.create_time "
			+ "from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id) b WHERE a.user_id=b.create_user"
			+ " and DATE_FORMAT(b.create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY chnl_name ORDER "
			+ "BY SUM DESC limit 5"))
	public List<Map<Object,Object>> getChnlByTop5();
	
	//统计客户节点所处的地域
	@Select(value = ("select b.COUNTY_NAME as city,count(distinct node_id)  as count from "
			+ "(select eqpt_id,node_id,province,city from bs_eqpt_info where NOW() BETWEEN begin_date and end_date "
			+ " UNION select eqpt_id,a.node_id,province,city from bs_o_work_eqpt a,bs_o_work b "
			+ " where a.work_id = b.work_id and a.modify_tag = '1') a,"
			+ "bs_p_county_info b where a.city=b.COUNTY_ID group by county_name"))
	public List<Map<Object,Object>> getCustomerNodeByCity();
	
	//行业分布统计
	@Select(value = ("select industry,count(*) as count from (select industry,cust_id from bs_cust  "
			+ "union select a.industry,a.cust_id from bs_o_cust a,bs_o_order b where a.order_id = b.order_id) b group by industry"))
	public List<Map<Object,Object>> getCountByIndustry();
	
	
	//查询所有产品名称
	@Select(value = ("select distinct prod_name,prod_type from bs_p_product where prod_state ='1'"))
	public List<Map<Object,Object>> getProductNameList();
		
	//更新本月的数据2
		@Update(value = ("update bs_monthly_task set task_value = #{0},task_value2 = #{1} where task_id = #{2} and year = #{3} and month = #{4} and task_key = #{5}"))
		public int updateBsTaskByValue(String taskValue,String taskValue2,String taskId,String year,String month,String taskKey);
		
	//查询本自然年内每月数据
	@Select(value = ("select task_value from bs_monthly_task where task_id = #{0} and year = #{1} order by month asc"))
	public List<Object> getSumByYear(String taskId , String year);
	
	//查询本自然年内每月数据2
	@Select(value = ("select month,task_value from bs_monthly_task where task_id = #{0} and year = #{1} order by month asc"))
	public List<Map<Object,Object>> getSumByYearMonth(String taskId , String year);
	
	//每月战区销售量统计（渠道结算金额）
	@Select(value = ("select task_key,task_value from bs_monthly_task where task_id = #{0} and year = #{1} and month = #{2} order by task_value desc limit 5"))
	public List<Map<Object,Object>> getSumByMonth(String taskId , String year,String month);
	//每月战区销售量统计2（渠道结算金额）
	@Select(value = ("select task_key,task_value,task_value2 from bs_monthly_task where task_id = #{0} and year = #{1} and month = #{2} order by task_value desc limit 5"))
	public List<Map<Object,Object>> getSumByMonth2(String taskId , String year,String month);
	//查询 年 月 内次战区的销量
	@Select(value = ("select task_value from bs_monthly_task where task_id = #{0} and year = #{1} and month = #{2} and task_key = #{3}"))
	public String getSumByChnlAndMonth(String taskId,String year,String month,String taskKey);
	//查询 年 月 内次战区的销量2
	@Select(value = ("select task_value from bs_monthly_task where task_id = #{0} and year = #{1} and month = #{2} and task_value2 = #{3}"))
	public String getSumByChnlAndMonth2(String taskId,String year,String month,String taskValue2);	
	//查询 年 内各月产品结算单的总额
	@Select(value = ("select month,task_value,task_value2 from bs_monthly_task where task_id = #{0} and year = #{1} and "
			+ "task_key = #{2} order by month  asc "))
	public List<Map<Object,Object>> getProductByMonth(String taskId,String year,String taskKey);
	
	//查询各个产品的销售总额
	@Select(value =("select task_key as name,sum(task_value) as value from bs_monthly_task where task_id = #{0} and year = #{1} group by name"))
	public List<Map<Object,Object>> getYearCountProduct(String taskId,String year);

	//查询各个套餐的销售总额
	@Select(value =("select task_key as name,sum(task_value) as value from bs_monthly_task where task_id = #{0} "
			+ "and year = #{1} and task_value2 = #{2} group by name"))
	public List<Map<Object,Object>> getYearCountDiscnt(String taskId,String year,String taskValue2);

}

